package ups.Database;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Hashtable;

import ups.Common.Departure;
import ups.Common.Port;
import ups.Common.Ship;

public class LateDatabase extends Database {
	
	private ShipDatabase shipDB;
	private PortDatabase portDB;
	private String LATE_TABLE = "late";
	
	private ArrayList<String> shipNames;
	private ArrayList<Port> ports;

	public LateDatabase(String hostName, int portNum, String databaseName, String username, String password) {
		super(hostName, portNum, databaseName, username, password);
		shipDB = new ShipDatabase(hostName, portNum, databaseName, username, password);
		portDB = new PortDatabase(hostName, portNum, databaseName, username, password);
		shipNames = shipDB.getShipNames();
		ports = portDB.getPorts();

	}
	
	public void put( Departure departure ) {
		Hashtable<String,String> dict = new Hashtable<String,String>();
		dict.put( "port_id", String.valueOf( this.indexOfPortNamed( departure.getPortName())));
		dict.put( "ship_id", String.valueOf( shipNames.indexOf( departure.getShipName())));
		dict.put( "route_id", String.valueOf( departure.getRouteID() ));
		dict.put( "date", getDateFormat().format( departure.getDate() ));
		dict.put( "scheduled_time", getTimeFormat().format( departure.getScheduledTime() ));
		dict.put( "actual_time", getTimeFormat().format( departure.getActualTime() ));
		
		/* We need to store the TIME DIFFERENCE between these two things! */
		Calendar scheduled = Calendar.getInstance();
		scheduled.setTime( departure.getScheduledTime());
		Calendar actual = Calendar.getInstance();
		actual.setTime( departure.getActualTime() );
			
		scheduled.roll( Calendar.HOUR_OF_DAY, -4);
		actual.roll( Calendar.HOUR_OF_DAY, -4);	
	
		int secondScheduled = scheduled.get( Calendar.HOUR_OF_DAY ) * 3600 +
							  scheduled.get( Calendar.MINUTE ) * 60 +
							  scheduled.get( Calendar.SECOND );
		
		int secondActual = actual.get( Calendar.HOUR_OF_DAY ) * 3600 +
						   actual.get( Calendar.MINUTE ) * 60 +
						   actual.get( Calendar.SECOND );
		
		
		int difference = Math.abs( secondActual - secondScheduled );

		dict.put( "late", "" + difference );
		
		super.put( LATE_TABLE, dict );
	}
	
	public int indexOfPortNamed( String name ) {
		int index;
		for( index = 0; index < ports.size(); index++ ) {
			if( ports.get( index ).getName().equals( name )) {
				return index;
			}
		}
		return -1;
	}
	
	public ArrayList<Departure> getDay( String shipName, Date date ) {
		ArrayList<Departure> departures = new ArrayList<Departure>();
		try {
			Statement stmt = this.getConnection().createStatement();
			String query = "SELECT * FROM late, ships, ports " +
						   "WHERE \"" + getDateFormat().format( date ) + "\" = late.date " +
						   "AND ships.name = \"" + shipName + "\" " +
						   "AND ships.ship_id = late.ship_id " +
						   "AND ports.port_id = late.port_id " +
						   "ORDER BY late.date, late.scheduled_time";
			//System.out.println( query );
			ResultSet rs = stmt.executeQuery( query );
			while( rs.next() ) {
				Departure depart = new Departure( rs.getString("ships.name"), rs.getString("ports.name"), rs.getInt("late.route_id"), getDateFormat().parse( rs.getString("date")), getTimeFormat().parse( rs.getString("scheduled_time")), getTimeFormat().parse( rs.getString("actual_time")) );
				departures.add( depart );
			}
			
		} catch ( Exception e ){
			e.printStackTrace();
		}
		return departures;
	}
	
	
}
